

create table #ZZMQ00(
	GLDateID	DECIMAL(19, 0),
	GODWFLAG2_1	INTEGER,
	GODWFLAG4_1	INTEGER,
	GODWFLAG6_1	INTEGER,
	GODWFLAG8_1	INTEGER)

insert into #ZZMQ00
select	a11.CompanyDateID  GLDateID,
	(Case when (a11.Dates <=  CONVERT(datetime, '2012-04-07 00:00:00', 120) and (not a11.Dates < DATEADD(DD, -89, '2012-04-07'))) then 1 else 0 end)  GODWFLAG2_1,
	(Case when (a11.Dates <=  CONVERT(datetime, '2012-04-07 00:00:00', 120) and (not a11.Dates < DATEADD(MM, 6, CONVERT(DATE, DATEADD(yy, DATEDIFF(yy, 0, DATEADD(MM, -6, '2012-04-07')), 0))))) then 1 else 0 end)  GODWFLAG4_1,
	(Case when (a11.Dates <=  DATEADD(MM, -12, '2012-04-07') and (not a11.Dates < DATEADD(MM, 6, CONVERT(DATE, DATEADD(yy, DATEDIFF(yy, 0, DATEADD(MM, -18, '2012-04-07')), 0))))) then 1 else 0 end)  GODWFLAG6_1,
	(Case when (a11.Dates <=  CONVERT(datetime, '2012-04-07 00:00:00', 120) and (not a11.Dates < DATEADD(DD, -6, '2012-04-07'))) then 1 else 0 end)  GODWFLAG8_1
from	MicroStrategy_Views.dbo.Common_Star_Dates	a11
where	((a11.Dates <=  CONVERT(datetime, '2012-04-07 00:00:00', 120)
 and (not a11.Dates < DATEADD(DD, -89, '2012-04-07')))
 or (a11.Dates <=  CONVERT(datetime, '2012-04-07 00:00:00', 120)
 and (not a11.Dates < DATEADD(MM, 6, CONVERT(DATE, DATEADD(yy, DATEDIFF(yy, 0, DATEADD(MM, -6, '2012-04-07')), 0)))))
 or (a11.Dates <=  DATEADD(MM, -12, '2012-04-07')
 and (not a11.Dates < DATEADD(MM, 6, CONVERT(DATE, DATEADD(yy, DATEDIFF(yy, 0, DATEADD(MM, -18, '2012-04-07')), 0)))))
 or (a11.Dates <=  CONVERT(datetime, '2012-04-07 00:00:00', 120)
 and (not a11.Dates < DATEADD(DD, -6, '2012-04-07')))) OPTION (MERGE JOIN, LOOP JOIN)

create table #ZZMD01(
	ShiptoNumber	INTEGER,
	ShiptoName0	CHAR(40),
	Category03Code	CHAR(3),
	WJXBFS1	FLOAT,
	WJXBFS2	FLOAT,
	WJXBFS3	FLOAT,
	WJXBFS4	FLOAT,
	WJXBFS5	FLOAT,
	WJXBFS6	FLOAT,
	WJXBFS7	INTEGER,
	WJXBFS8	FLOAT,
	WJXBFS9	FLOAT)

insert into #ZZMD01
select	a14.ShiptoNumber  ShiptoNumber,
	a14.ShiptoName  ShiptoName0,
	a14.Category03Code  Category03Code,
	sum(case when DatePart( DW , a13.Dates) = 2 then a11.ExtendedPrice else  null end)  WJXBFS1,
	sum(case when DatePart( DW , a13.Dates) = 3 then a11.ExtendedPrice else  null end)  WJXBFS2,
	sum(case when DatePart( DW , a13.Dates) = 4 then a11.ExtendedPrice else  null end)  WJXBFS3,
	sum(case when DatePart( DW , a13.Dates) = 5 then a11.ExtendedPrice else  null end)  WJXBFS4,
	sum(case when DatePart( DW , a13.Dates) = 6 then a11.ExtendedPrice else  null end)  WJXBFS5,
	sum(case when DatePart( DW , a13.Dates) = 7 then a11.ExtendedPrice else  null end)  WJXBFS6,
	count(distinct a11.GLDateID)  WJXBFS7,
	sum(case when DatePart( DW , a13.Dates) >= 1 then a11.ExtendedPrice else  null end)  WJXBFS8,
	sum(case when DatePart( DW , a13.Dates) >= 1 then (ISNULL(a11.ExtendedPrice, 0) - ISNULL(a11.ExtendedCost, 0)) else  null end)  WJXBFS9
from	MicroStrategy_Views.dbo.Sales_Star_Facts	a11
	join	#ZZMQ00	pa12
	  on 	(a11.GLDateID = pa12.GLDateID)
	join	MicroStrategy_Views.dbo.Common_Star_Dates	a13
	  on 	(a11.GLDateID = a13.CompanyDateID)
	join	MicroStrategy_Views.dbo.Sales_Star_Customer	a14
	  on 	(a11.CustomerNumberID = a14.CustomerNumberID)
where	(a14.Category06Code in ('O00')
 and a14.ShiptoSearchTypeCode not in ('CCX', 'CFX', 'RT ', 'RP ', 'SP ')
 and pa12.GODWFLAG8_1 = 1)
group by	a14.ShiptoNumber,
	a14.ShiptoName,
	a14.Category03Code OPTION (MERGE JOIN, LOOP JOIN)

create table #ZZMD02(
	ShiptoNumber	INTEGER,
	ShiptoName0	CHAR(40),
	Category03Code	CHAR(3),
	WJXBFS1	FLOAT,
	WJXBFS2	FLOAT,
	WJXBFS3	FLOAT,
	WJXBFS4	FLOAT,
	WJXBFS5	INTEGER)

insert into #ZZMD02
select	a13.ShiptoNumber  ShiptoNumber,
	a13.ShiptoName  ShiptoName0,
	a13.Category03Code  Category03Code,
	sum((Case when pa12.GODWFLAG4_1 = 1 then a11.ExtendedPrice else NULL end))  WJXBFS1,
	sum((Case when pa12.GODWFLAG4_1 = 1 then (ISNULL(a11.ExtendedPrice, 0) - ISNULL(a11.ExtendedCost, 0)) else NULL end))  WJXBFS2,
	sum((Case when pa12.GODWFLAG6_1 = 1 then a11.ExtendedPrice else NULL end))  WJXBFS3,
	sum((Case when pa12.GODWFLAG6_1 = 1 then (ISNULL(a11.ExtendedPrice, 0) - ISNULL(a11.ExtendedCost, 0)) else NULL end))  WJXBFS4,
	(Case when max((Case when pa12.GODWFLAG2_1 = 1 then 1 else 0 end)) = 1 then count(distinct (Case when pa12.GODWFLAG2_1 = 1 then a11.OrderNumber else NULL end)) else NULL end)  WJXBFS5
from	MicroStrategy_Views.dbo.Sales_Star_Facts	a11
	join	#ZZMQ00	pa12
	  on 	(a11.GLDateID = pa12.GLDateID)
	join	MicroStrategy_Views.dbo.Sales_Star_Customer	a13
	  on 	(a11.CustomerNumberID = a13.CustomerNumberID)
where	(a13.Category06Code in ('O00')
 and a13.ShiptoSearchTypeCode not in ('CCX', 'CFX', 'RT ', 'RP ', 'SP ')
 and (pa12.GODWFLAG4_1 = 1
 or pa12.GODWFLAG6_1 = 1
 or pa12.GODWFLAG2_1 = 1))
group by	a13.ShiptoNumber,
	a13.ShiptoName,
	a13.Category03Code OPTION (MERGE JOIN, LOOP JOIN)

create table #ZZMD03(
	ShiptoNumber	INTEGER,
	ShiptoName0	CHAR(40),
	Category03Code	CHAR(3),
	WJXBFS1	INTEGER,
	WJXBFS2	INTEGER,
	WJXBFS3	INTEGER,
	WJXBFS4	INTEGER,
	WJXBFS5	INTEGER,
	WJXBFS6	INTEGER,
	WJXBFS7	INTEGER)

insert into #ZZMD03
select	a13.ShiptoNumber  ShiptoNumber,
	a13.ShiptoName  ShiptoName0,
	a13.Category03Code  Category03Code,
	count(a11.[Delivery Date])  WJXBFS1,
	(Case when max((Case when (a11.[Delivery Date] <=  '2012-04-07' and (not a11.[Delivery Date] < DATEADD(DD, -6, '2012-04-07')) and DATEPART(WEEKDAY, a11.[Delivery Date]) = 6) then 1 else 0 end)) = 1 then count(distinct (Case when (a11.[Delivery Date] <=  '2012-04-07' and (not a11.[Delivery Date] < DATEADD(DD, -6, '2012-04-07')) and DATEPART(WEEKDAY, a11.[Delivery Date]) = 6) then a11.[Delivery Date] else NULL end)) else NULL end)  WJXBFS2,
	(Case when max((Case when (a11.[Delivery Date] <=  '2012-04-07' and (not a11.[Delivery Date] < DATEADD(DD, -6, '2012-04-07')) and DATEPART(WEEKDAY, a11.[Delivery Date]) = 2) then 1 else 0 end)) = 1 then count(distinct (Case when (a11.[Delivery Date] <=  '2012-04-07' and (not a11.[Delivery Date] < DATEADD(DD, -6, '2012-04-07')) and DATEPART(WEEKDAY, a11.[Delivery Date]) = 2) then a11.[Delivery Date] else NULL end)) else NULL end)  WJXBFS3,
	(Case when max((Case when (a11.[Delivery Date] <=  '2012-04-07' and (not a11.[Delivery Date] < DATEADD(DD, -6, '2012-04-07')) and DATEPART(WEEKDAY, a11.[Delivery Date]) = 7) then 1 else 0 end)) = 1 then count(distinct (Case when (a11.[Delivery Date] <=  '2012-04-07' and (not a11.[Delivery Date] < DATEADD(DD, -6, '2012-04-07')) and DATEPART(WEEKDAY, a11.[Delivery Date]) = 7) then a11.[Delivery Date] else NULL end)) else NULL end)  WJXBFS4,
	(Case when max((Case when (a11.[Delivery Date] <=  '2012-04-07' and (not a11.[Delivery Date] < DATEADD(DD, -6, '2012-04-07')) and DATEPART(WEEKDAY, a11.[Delivery Date]) = 5) then 1 else 0 end)) = 1 then count(distinct (Case when (a11.[Delivery Date] <=  '2012-04-07' and (not a11.[Delivery Date] < DATEADD(DD, -6, '2012-04-07')) and DATEPART(WEEKDAY, a11.[Delivery Date]) = 5) then a11.[Delivery Date] else NULL end)) else NULL end)  WJXBFS5,
	(Case when max((Case when (a11.[Delivery Date] <=  '2012-04-07' and (not a11.[Delivery Date] < DATEADD(DD, -6, '2012-04-07')) and DATEPART(WEEKDAY, a11.[Delivery Date]) = 3) then 1 else 0 end)) = 1 then count(distinct (Case when (a11.[Delivery Date] <=  '2012-04-07' and (not a11.[Delivery Date] < DATEADD(DD, -6, '2012-04-07')) and DATEPART(WEEKDAY, a11.[Delivery Date]) = 3) then a11.[Delivery Date] else NULL end)) else NULL end)  WJXBFS6,
	(Case when max((Case when (a11.[Delivery Date] <=  '2012-04-07' and (not a11.[Delivery Date] < DATEADD(DD, -6, '2012-04-07')) and DATEPART(WEEKDAY, a11.[Delivery Date]) = 4) then 1 else 0 end)) = 1 then count(distinct (Case when (a11.[Delivery Date] <=  '2012-04-07' and (not a11.[Delivery Date] < DATEADD(DD, -6, '2012-04-07')) and DATEPART(WEEKDAY, a11.[Delivery Date]) = 4) then a11.[Delivery Date] else NULL end)) else NULL end)  WJXBFS7
from	MicroStrategy_Views.dbo.Common_MasterDeliveryDateCalendar	a11
	join	MicroStrategy_Views.dbo.Common_DeliveryDayCodeDetails	a12
	  on 	(a11.[Delivery Cycle] = a12.DeliveryCycle)
	join	MicroStrategy_Views.dbo.Sales_Star_Customer	a13
	  on 	(a12.Frequency = a13.StopCode)
where	(a13.Category06Code in ('O00')
 and a13.ShiptoSearchTypeCode not in ('CCX', 'CFX', 'RT ', 'RP ', 'SP '))
group by	a13.ShiptoNumber,
	a13.ShiptoName,
	a13.Category03Code OPTION (MERGE JOIN, LOOP JOIN)

create table #ZZMD04(
	ShiptoNumber	INTEGER,
	ShiptoName0	CHAR(40),
	Category03Code	CHAR(3),
	WJXBFS1	DATETIME,
	WJXBFS2	DATETIME)

insert into #ZZMD04
select	a12.ShiptoNumber  ShiptoNumber,
	a12.ShiptoName  ShiptoName0,
	a12.Category03Code  Category03Code,
	max(a11.Dates)  WJXBFS1,
	DATEDIFF(DD, max(a11.Dates), '2012-04-07')  WJXBFS2
from	MicroStrategy_Views.dbo.Common_Star_Dates	a11
	join	MicroStrategy_Views.dbo.Sales_Star_Customer	a12
	  on 	(a11.CompanyDateID = a12.LastInvoiceDate)
where	(a12.Category06Code in ('O00')
 and a12.ShiptoSearchTypeCode not in ('CCX', 'CFX', 'RT ', 'RP ', 'SP '))
group by	a12.ShiptoNumber,
	a12.ShiptoName,
	a12.Category03Code OPTION (MERGE JOIN, LOOP JOIN)

create table #ZZMD05(
	ShiptoNumber	INTEGER,
	ShiptoName0	CHAR(40),
	Category03Code	CHAR(3),
	WJXBFS1	FLOAT,
	WJXBFS2	FLOAT)

insert into #ZZMD05
select	a14.ShiptoNumber  ShiptoNumber,
	a14.ShiptoName  ShiptoName0,
	a14.Category03Code  Category03Code,
	sum(case when DatePart( DW , a13.Dates) >= 1 then (ISNULL(a11.ExtendedPrice, 0) - ISNULL(a11.ExtendedCost, 0)) else  null end)  WJXBFS1,
	sum(case when DatePart( DW , a13.Dates) >= 1 then a11.ExtendedPrice else  null end)  WJXBFS2
from	MicroStrategy_Views.dbo.Sales_Star_Facts	a11
	join	#ZZMQ00	pa12
	  on 	(a11.GLDateID = pa12.GLDateID)
	join	MicroStrategy_Views.dbo.Common_Star_Dates	a13
	  on 	(a11.GLDateID = a13.CompanyDateID)
	join	MicroStrategy_Views.dbo.Sales_Star_Customer	a14
	  on 	(a11.CustomerNumberID = a14.CustomerNumberID)
where	(a14.Category06Code in ('O00')
 and a14.ShiptoSearchTypeCode not in ('CCX', 'CFX', 'RT ', 'RP ', 'SP ')
 and pa12.GODWFLAG2_1 = 1)
group by	a14.ShiptoNumber,
	a14.ShiptoName,
	a14.Category03Code OPTION (MERGE JOIN, LOOP JOIN)

select	distinct a115.Category06Code  Category06Code,
	a115.Category06  Category06,
	a115.Category05Code  Category05Code,
	a115.Category05  Category05,
	a115.Category04Code  Category04Code,
	a115.Category04  Category04,
	coalesce(pa12.Category03Code, pa13.Category03Code, pa15.Category03Code, pa112.Category03Code, pa113.Category03Code)  Category03Code,
	a115.Category03  Category03,
	coalesce(pa12.ShiptoNumber, pa13.ShiptoNumber, pa15.ShiptoNumber, pa112.ShiptoNumber, pa113.ShiptoNumber)  ShiptoNumber,
	a116.Days  Days,
	coalesce(pa12.ShiptoName0, pa13.ShiptoName0, pa15.ShiptoName0, pa112.ShiptoName0, pa113.ShiptoName0)  ShiptoName0,
	pa12.WJXBFS1  WJXBFS1,
	pa12.WJXBFS2  WJXBFS2,
	pa12.WJXBFS3  WJXBFS3,
	pa12.WJXBFS4  WJXBFS4,
	pa12.WJXBFS5  WJXBFS5,
	pa12.WJXBFS6  WJXBFS6,
	pa12.WJXBFS8  WJXBFS7,
	pa12.WJXBFS7  WJXBFS8,
	pa13.WJXBFS1  WJXBFS9,
	pa13.WJXBFS3  WJXBFSa,
	pa13.WJXBFS2  WJXBFSb,
	pa13.WJXBFS4  WJXBFSc,
	pa15.WJXBFS1  WJXBFSd,
	pa15.WJXBFS2  WJXBFSe,
	pa15.WJXBFS3  WJXBFSf,
	pa15.WJXBFS4  WJXBFS10,
	pa15.WJXBFS5  WJXBFS11,
	pa15.WJXBFS6  WJXBFS12,
	pa15.WJXBFS7  WJXBFS13,
	pa112.WJXBFS1  WJXBFS14,
	pa112.WJXBFS2  WJXBFS15,
	pa113.WJXBFS1  WJXBFS16,
	pa113.WJXBFS2  WJXBFS17,
	pa12.WJXBFS9  WJXBFS18,
	pa13.WJXBFS5  WJXBFS19
from	#ZZMD01	pa12
	full outer join	#ZZMD02	pa13
	  on 	(pa12.Category03Code = pa13.Category03Code and
	pa12.ShiptoName0 = pa13.ShiptoName0 and
	pa12.ShiptoNumber = pa13.ShiptoNumber)
	full outer join	#ZZMD03	pa15
	  on 	(coalesce(pa12.Category03Code, pa13.Category03Code) = pa15.Category03Code and
	coalesce(pa12.ShiptoName0, pa13.ShiptoName0) = pa15.ShiptoName0 and
	coalesce(pa12.ShiptoNumber, pa13.ShiptoNumber) = pa15.ShiptoNumber)
	full outer join	#ZZMD04	pa112
	  on 	(coalesce(pa12.Category03Code, pa13.Category03Code, pa15.Category03Code) = pa112.Category03Code and
	coalesce(pa12.ShiptoName0, pa13.ShiptoName0, pa15.ShiptoName0) = pa112.ShiptoName0 and
	coalesce(pa12.ShiptoNumber, pa13.ShiptoNumber, pa15.ShiptoNumber) = pa112.ShiptoNumber)
	full outer join	#ZZMD05	pa113
	  on 	(coalesce(pa12.Category03Code, pa13.Category03Code, pa15.Category03Code, pa112.Category03Code) = pa113.Category03Code and
	coalesce(pa12.ShiptoName0, pa13.ShiptoName0, pa15.ShiptoName0, pa112.ShiptoName0) = pa113.ShiptoName0 and
	coalesce(pa12.ShiptoNumber, pa13.ShiptoNumber, pa15.ShiptoNumber, pa112.ShiptoNumber) = pa113.ShiptoNumber)
	join	MicroStrategy_Views.dbo.Sales_Star_Customer	a115
	  on 	(coalesce(pa12.Category03Code, pa13.Category03Code, pa15.Category03Code, pa112.Category03Code, pa113.Category03Code) = a115.Category03Code and
	coalesce(pa12.ShiptoName0, pa13.ShiptoName0, pa15.ShiptoName0, pa112.ShiptoName0, pa113.ShiptoName0) = a115.ShiptoName and
	coalesce(pa12.ShiptoNumber, pa13.ShiptoNumber, pa15.ShiptoNumber, pa112.ShiptoNumber, pa113.ShiptoNumber) = a115.ShiptoNumber)
	join	MicroStrategy_Views.dbo.REL_CUSTOMER_DAYS	a116
	  on 	(a115.ShiptoNumber = a116.ShiptoNumber and
	coalesce(pa12.ShiptoNumber, pa13.ShiptoNumber, pa15.ShiptoNumber, pa112.ShiptoNumber, pa113.ShiptoNumber) = a116.ShiptoNumber)


drop table #ZZMQ00

drop table #ZZMD01

drop table #ZZMD02

drop table #ZZMD03

drop table #ZZMD04

drop table #ZZMD05

